package gen.tools.cra;

import gen.tools.Widgets;
import gen.tools.structures.CraObj;
import gen.tools.structures.CreateCRA;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class CreateCra extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    public CreateCra() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		final Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/text");
		
		final int changeLogType = 19; //開發人員提出異動模組申請
		
		final CreateCRA cras = new Gson().fromJson( request.getParameter("craRecord"), CreateCRA.class);
		final String subsysId = cras.getSubsysId();
		final String applierId = cras.getCreatorId();
		
		String strSQL;
		String res ="Done";
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = wid.getConn();
			conn.setAutoCommit(false);	//為取得序號，先將提交關閉
			
			/****建立LOG，第一步就開始記錄程式的執行****/
			strSQL="INSERT INTO changelog (changeLogId,changeTypeId,creatorId,bDate) VALUES (null,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, changeLogType );
			ps.setString(2, applierId);
			ps.setTimestamp(3, wid.cDT());
			ps.execute();
			strSQL="SELECT MAX(changeLogId) FROM changelog";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			final int craChangLogId = rs.getInt(1);
			
			/****創建變更申請****/
			strSQL = "INSERT INTO cramain (craId,subsysId,craStatusId,craTypeId,creatorId,cDT) VALUES (null,?,?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, subsysId);
			ps.setInt(2, 7);	//開發人員提出異動模組變更申請
			ps.setInt(3, 3);	//3 變更模組
			ps.setString(4, applierId);
			ps.setTimestamp(5, wid.cDT());
			ps.execute();

			//保存新產生的變更申請序號，等等創建細項要用
			strSQL="SELECT MAX(craId) FROM cramain";
			ps = conn.prepareStatement(strSQL);
			rs = ps.executeQuery();
			rs.next();
			final int craId = rs.getInt(1);		//變更申請序號ID
			
			conn.commit();
			conn.setAutoCommit(true);
			
			/****創建變更申請細項****/
			for(int i=0;i<cras.getCraObj().size();i++){
				CraObj modUnit = cras.getCraObj().get(i);
				final String modId = modUnit.getModId();

				strSQL = "INSERT INTO cradetail (craId,craDetId,modUnitId) VALUES (?,?,?)";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, craId);
				ps.setInt(2, (i+1) );	//目前新增只會有一筆的變更申請細項
				ps.setString(3, modId );
				ps.execute();
				
				/****變更申依據需求****/
				strSQL="INSERT INTO mpcrareq (craId,craDetId,reqId) VALUES (?,?,?)";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, craId);
				ps.setInt(2, (i+1) );
				for(int j=0; j<modUnit.getReqs().size(); j++) {
					ps.setInt(3, modUnit.getReqs().get(j).getReqId());
					ps.execute();
				}
				
				/****變更申請可操作人員****/
				strSQL="INSERT INTO mpcraperformers (craId,craDetId,performerId,isRole) VALUES (?,?,?,?)";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, craId);
				ps.setInt(2, (i+1));
				for(int j=0; j<modUnit.getPerformers().size(); j++) {
					ps.setString(3, modUnit.getPerformers().get(j).getPerformerId());
					ps.setString(4, modUnit.getPerformers().get(j).isRole()?"t":"f");	//這個ID，是角色ID還是員工ID
					ps.execute();
				}
			}
			

			/****產生待辦事項****/
			strSQL="INSERT INTO todolist (subsysId,dataType,masterId,roleId,cDT,creatorId) VALUES (?,?,?,?,?,?)";
			ps = conn.prepareStatement(strSQL);
			ps.setString(1, cras.getSubsysId());
			ps.setInt(2, 5);				//主檔類型，1表示這個ID是來自變更申請
			ps.setString(3, String.valueOf(craId) );			//主檔ID
			ps.setInt(4, 4);				//角色ID
			ps.setTimestamp(5, wid.cDT());
			ps.setString(6, applierId);
			ps.execute();
			
			/****將這筆變更申請目前的進度給修改****/
			strSQL = "UPDATE cramain SET craStatusId=? WHERE craId=?";
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, 8);		//開發人員提出異動模組變更申請待審
			ps.setInt(2, craId);
			ps.execute();
			
			/****完成這次的異動****/
			strSQL = "UPDATE changelog SET eDate=? WHERE changeLogId=?";
			ps = conn.prepareStatement(strSQL);
			ps.setTimestamp(1, wid.cDT());
			ps.setInt(2, craChangLogId );
			ps.execute();
		
		} catch(Exception e) {
			res = wid.errProcess(e);
			wid.errLog(e);
		} finally {
			try {
				if ( rs !=null ) {rs.close();}
				if ( ps !=null ) {ps.close();}
				if ( conn !=null ) {conn.close();}	
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	    
	    response.getWriter().write( res );
	}

}
